﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data;
using Dapper;
using System.Text.RegularExpressions;

namespace _180FrameWork.DataBaseHelper.SqlServerExtend
{
    public class SqlServerJintDB : IJintDB
    {
        private Database _db;

        private DbConnection _Conn;

        private DbConnection Conn
        {
            get
            {
                if (_Conn == null)
                    _Conn = this._db.CreateConnection();
                return this._Conn;
            }
        }

        private string _paramChar = "@";

        public SqlServerJintDB(string DBConnectionString)
        {
            _db = new SqlDatabase(DBConnectionString);
        }
        #region Dapper

        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public dynamic Find(string sql)
        {
            return this.Conn.Query(sql).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).FirstOrDefault();
        }
        /// <summary>
        /// 查询单个
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public dynamic Find(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).FirstOrDefault();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql)
        {
            return this.Conn.Query(sql).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Query(sql, GetParams(sql, param)).ToList();
        }
        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public IList<dynamic> Query(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Query(sql, GetParams(sql, param), tran).ToList();
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql)
        {
            return Page(sql, new Dictionary<string, object>());
        }
        /// <summary>
        /// 查询分页
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <param name="sort"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public PageData<dynamic> Page(string sql, IDictionary<string, object> param)
        {
            int page = 1; int rows = 20; string sort = ""; string order = ""; int total = 0; string SUM = "";
            foreach (KeyValuePair<string, object> item in param)
            {
                switch (item.Key)
                {
                    case "page":
                        page = Convert.ToInt32(item.Value);
                        break;
                    case "rows":
                        rows = Convert.ToInt32(item.Value);
                        break;
                    case "sort":
                        sort = Convert.ToString(item.Value);
                        break;
                    case "order":
                        order = Convert.ToString(item.Value);
                        break;
                    case "total":
                        total = Convert.ToInt32(item.Value);
                        break;
                    case "sum":
                        SUM = Convert.ToString(item.Value);
                        break;
                    //default:
                    //    dp.Add(item.Key, item.Value);
                    //    break;
                }
            }
            var dp = GetParams(sql, param);
            var p = new PageData<dynamic>();
            if (total != 0)
            {
                p.total = total;
            }
            else
            {
                string strTotalSql = "select count(*) from(" + sql + ") t";
                p.total = this.Conn.ExecuteScalar<int>(strTotalSql, dp);
                if (p.total == 0)
                    p.total = 1;
            }
            p.totalPages = (p.total / rows) + (p.total % rows > 0 ? 1 : 0);
            //汇总
            if (SUM != "")
            {
                string strSumSql = "select " + SUM + " from(" + sql + ") t";
                p.footer = this.Conn.Query(strSumSql, dp).ToList();
            }
            //排序
            string orderby = "";
            if (sort != "")
            {
                orderby = " order by " + sort;
                if (order != "")
                {
                    orderby += " " + order;
                }
            }
            else
            {
                orderby = " order by (select null)";
            }
            //获取分页数据
            int beginrow = rows * (page - 1) + 1;
            int endrow = rows * page;
            dp.Add("BEGINROW", beginrow);
            dp.Add("ENDROW", endrow);
            string strDataSql = "select c.* from(select t.*,row_number() over (" + orderby + ") AS RN from(select x.* from(" + sql + ")x )t )c WHERE C.RN between @BEGINROW and @ENDROW ";
            p.rows = this.Conn.Query(strDataSql, dp).ToList();
            return p;
        }
        /*
    /// <summary>
    /// 查找表格记录
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public GridData<dynamic> Grid(string sql)
    {
        return Grid(sql, new Dictionary<string, object>());
    }
    /// <summary>
    /// 查找表格记录
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public GridData<dynamic> Grid(string sql, IDictionary<string, object> param)
    {
        string sort = ""; string order = ""; string SUM = "";
        foreach (KeyValuePair<string, object> item in param)
        {
            switch (item.Key)
            {                   
                case "sort":
                    sort = Convert.ToString(item.Value);
                    break;
                case "order":
                    order = Convert.ToString(item.Value);
                    break;               
                case "sum":
                    SUM = Convert.ToString(item.Value);
                    break;
                //default:
                //    dp.Add(item.Key, item.Value);
                //    break;
            }
        }
        var dp = GetParams(sql, param);
        var p = new GridData<dynamic>();
           
        //汇总
        if (SUM != "")
        {
            string strSumSql = "select " + SUM + " from(" + sql + ") t";
            p.footer = this.Conn.Query(strSumSql, dp).ToList();
        }
        //排序
        string orderby = "";
        if (sort != "")
        {
            orderby = " order by " + sort;
            if (order != "")
            {
                orderby += " " + order;
            }
        }
        else
        {
            orderby = " order by (select null)";
        }
        p.rows = this.Conn.Query(sql, dp).ToList();
        return p;
    }
    
    /// <summary>
    /// 查找标准数据结构
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable QueryDataTable(string sql)
    {
        return QueryDataTable(sql, null);
    }
    /// <summary>
    /// 查找标准数据结构
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public DataTable QueryDataTable(string sql, IDictionary<string, object> param)
    {
        DbCommand command = _db.GetSqlStringCommand(sql);
        if (param != null)
        {
            foreach (string strPara in param.Keys)
            {
                DbParameter para = command.CreateParameter();
                para.ParameterName = strPara;
                if (param[strPara] != null)
                    para.Value = param[strPara];
                else
                    para.Value = DBNull.Value;
                command.Parameters.Add(para);
            }
        }
        DataSet ds = _db.ExecuteDataSet(command);
        return ds.Tables[0];
    }
     */ 
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Exec(string sql)
        {
            return this.Conn.Execute(sql);
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param)
        {
            return this.Conn.Execute(sql, GetParams(sql, param));
        }
        /// <summary>
        /// 执行语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public int Exec(string sql, IDictionary<string, object> param, IDbTransaction tran)
        {
            return this.Conn.Execute(sql, GetParams(sql, param), tran);
        }


        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="param"></param>
        public IDictionary<string, object> Sp(string spName, IDictionary<string, object>[] param) { return null; }

        /// <summary>
        /// 开启事务
        /// </summary>
        /// <returns></returns>
        public IDbTransaction BeginTran()
        {
            return this.Conn.BeginTransaction();
        }
        /// <summary>
        /// 开启连接
        /// </summary>
        public void Open()
        {
            this.Conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            this.Conn.Close();
        }

        private DynamicParameters GetParams(string SQL, IDictionary<string, object> data)
        {
            var param = new DynamicParameters();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (mapList.Contains(this._paramChar + prop.Key))
                    {
                        if (prop.Value != null)
                        {
                            if (prop.Value.GetType().Name.ToUpper() == "STRING")
                            {
                                param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                            }
                            else
                            {
                                param.Add(prop.Key, prop.Value);
                            }
                        }
                        else
                        {
                            param.Add(prop.Key, null);
                        }
                    }
                }
            }
            else
            {

                foreach (KeyValuePair<string, object> prop in data)
                {
                    if (prop.Value != null)
                    {
                        if (prop.Value.GetType().Name.ToUpper() == "STRING")
                        {
                            param.Add(prop.Key, new DbString() { Value = prop.Value.ToString(), IsAnsi = true });
                        }
                        else
                        {
                            param.Add(prop.Key, prop.Value.ToString());
                        }
                    }
                    else
                    {
                        param.Add(prop.Key, null);
                    }
                }
            }
            return param;
        }

        public IList<string> Match(string SQL, IDictionary<string, object> param)
        {
            IList<string> NoMatchList = new List<string>();
            if (this._paramChar != "")
            {
                var mapList = Regex.Matches(SQL, @"\" + this._paramChar + @"\w+").Cast<Match>().Select(m => m.Value).Distinct().ToArray();
                foreach (var p in mapList)
                {
                    string key = p.Replace(this._paramChar, "");
                    bool IsExist = false;
                    foreach (KeyValuePair<string, object> prop in param)
                    {
                        if (prop.Key == key)
                        {
                            IsExist = true;
                            break;
                        }
                    }
                    if (!IsExist)
                    {
                        NoMatchList.Add(key);
                    }
                }
            }
            return NoMatchList;
        }

        public void ChangeDatabase(string databaseName)
        {
            this.Conn.ChangeDatabase(databaseName);
        }
        #endregion
    }
}
